The Script Editor is an interface for writing PQL, MDX and SQL scripts. It is available in multiple venues throughout the application for advanced operations. This includes:
- The formulate tools which have a script mode
- Custom Calculations in Model
- Member Security in the Data Source Manager
The language presented to the user depends on the data source, context and functionality.
Editor Interface
The Editor is made up of a scripting window, Functions, Columns, and Elements.
The Functions panel exposes different libraries depending on the specific capability. In this example it shows the Semantic and Common library, organized into logical groups.
To select an expression, open the relevant group; either drag the expression onto the scripting window, or double click on it. In the image below, the AddMonths function was added from the Date-Time category.
Hovering over the function will show you
- A brief description of the function
- A function mask showing you what is expected as input arguments
- The type of value the function will return.
Right clicking on the function will expose a help menu. Clicking on it will launch the specific help topic in online help.
Any required arguments are with placeholder text "<.............>".
Insert the arguments by selecting the required columns , elements or values then click Apply:
The script is then usually added back into the window. and you can continue from there.
Example
In the example below, the BottomPercent function is used to build a custom set of the bottom 10% of Products by Sales. First, the BottomPercent function is added to the scripting window:
A list of all members in the Product hierarchy is inserted in the List argument; the integer specified is 10; and the Sales measure is given as the third optional argument:
Once you've finished writing your script, click Apply.